﻿Imports ccapps.Web.page
Imports System.Data.OracleClient

Namespace persistent
    Public Class Gpo2Gpo

        Private Shared ConnStr As String = GlobalVar.OraConnStr

        Private Shared dbPackageName As String = "eu_bill.GPO2GPO."


        Public Shared Function CreateCustomer(
                                              ByVal DDL_ADR_NATIONALITY As Integer,
                                              ByVal TB_CS_TC_ID As String,
                                              ByVal TB_ADR_FNAME As String,
                                              ByVal TB_ADR_LNAME As String,
                                              ByVal TB_CS_REMARK1 As String,
                                              ByVal TB_CS_BIRTH_PROVINCE As String,
                                              ByVal TB_CS_REMARK2 As String,
                                              ByVal TDD_ADR_BIRTHDT As String,
                                              ByVal TB_CS_REG_PROVINCE As String,
                                              ByVal TB_CS_REG_DISTRICT As String,
                                              ByVal DDL_ADR_SEX As String,
                                              ByVal DDL_IDTYPE_CODE As Integer,
                                              ByVal TB_ADR_IDNO As String,
                                              ByVal DLL_PRG_CODE As String,
                                              ByVal DLL_RPCODE As Integer,
                                              ByVal DLL_RS_CODE As Integer,
                                              ByVal TB_ADR_ROLES As String,
                                              ByVal DDL1_ADR_CITY As String,
                                              ByVal DDL1_ADR_NOTE1 As String,
                                              ByVal TB1_ADR_STREET As String,
                                              ByVal TB1_ADR_ZIP As String,
                                              ByVal TB1_ADR_STREETNO As String,
                                              ByVal TB_ADR_PHN1_AREA As String,
                                              ByVal TB_ADR_PHN1 As String,
                                              ByVal TB_ADR_PHN2_AREA As String,
                                              ByVal TB_ADR_PHN2 As String,
                                              ByVal TB_ADR_SMSNO As String,
                                              ByVal TB_ADR_EMAIL As String,
                                              ByVal TB_ADR_NOTE2 As String,
                                               ByVal LBL_Mernis_Status As String,
                                              ByRef MESSAGE As String) As DataTable





            Dim command As New OracleCommand(dbPackageName & "CreateCustomer")


            command.Parameters.Add("pin_ADR_NATIONALITY", OracleType.Int32).Value = DDL_ADR_NATIONALITY
            command.Parameters.Add("pin_CS_TC_ID", OracleType.VarChar).Value = TB_CS_TC_ID
            command.Parameters.Add("pin_ADR_FNAME", OracleType.VarChar).Value = TB_ADR_FNAME
            command.Parameters.Add("pin_ADR_LNAME", OracleType.VarChar).Value = TB_ADR_LNAME
            command.Parameters.Add("pin_CS_REMARK1", OracleType.VarChar).Value = TB_CS_REMARK1
            command.Parameters.Add("pin_CS_BIRTH_PROVINCE", OracleType.VarChar).Value = TB_CS_BIRTH_PROVINCE
            command.Parameters.Add("pin_CS_REMARK2", OracleType.VarChar).Value = TB_CS_REMARK2
            command.Parameters.Add("pin_ADR_BIRTHDT", OracleType.VarChar).Value = TDD_ADR_BIRTHDT
            command.Parameters.Add("pin_CS_REG_PROVINCE", OracleType.VarChar).Value = TB_CS_REG_PROVINCE
            command.Parameters.Add("pin_CS_REG_DISTRICT", OracleType.VarChar).Value = TB_CS_REG_DISTRICT
            command.Parameters.Add("pin_ADR_SEX", OracleType.Char).Value = DDL_ADR_SEX
            command.Parameters.Add("pin_IDTYPE_CODE", OracleType.Int32).Value = DDL_IDTYPE_CODE
            command.Parameters.Add("pin_ADR_IDNO", OracleType.VarChar).Value = TB_ADR_IDNO
            command.Parameters.Add("pin_PRG_CODE", OracleType.VarChar).Value = DLL_PRG_CODE
            command.Parameters.Add("pin_RPCODE", OracleType.Int32).Value = DLL_RPCODE
            command.Parameters.Add("pin_RS_CODE", OracleType.Int32).Value = DLL_RS_CODE
            command.Parameters.Add("pin_ADR_ROLES", OracleType.VarChar).Value = TB_ADR_ROLES
            command.Parameters.Add("pin_ADR_CITY", OracleType.VarChar).Value = DDL1_ADR_CITY
            command.Parameters.Add("pin_ADR_NOTE1", OracleType.VarChar).Value = DDL1_ADR_NOTE1
            command.Parameters.Add("pin_ADR_STREET", OracleType.VarChar).Value = TB1_ADR_STREET
            command.Parameters.Add("pin_ADR_ZIP", OracleType.VarChar).Value = TB1_ADR_ZIP
            command.Parameters.Add("pin_ADR_STREETNO", OracleType.VarChar).Value = TB1_ADR_STREETNO
            command.Parameters.Add("pin_ADR_PHN1_AREA", OracleType.VarChar).Value = TB_ADR_PHN1_AREA
            command.Parameters.Add("pin_ADR_PHN1", OracleType.VarChar).Value = TB_ADR_PHN1
            command.Parameters.Add("pin_ADR_PHN2_AREA", OracleType.VarChar).Value = TB_ADR_PHN2_AREA
            command.Parameters.Add("pin_ADR_PHN2", OracleType.VarChar).Value = TB_ADR_PHN2
            command.Parameters.Add("pin_ADR_SMSNO", OracleType.VarChar).Value = TB_ADR_SMSNO
            command.Parameters.Add("pin_ADR_EMAIL", OracleType.VarChar).Value = TB_ADR_EMAIL
            command.Parameters.Add("pin_ADR_NOTE2", OracleType.VarChar).Value = TB_ADR_NOTE2
            command.Parameters.Add("pin_cs_data_level", OracleType.VarChar).Value = LBL_Mernis_Status
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output 'Customer ID Bilgisinin Databaseden Alınması 

            Return ExecuteDataTable(command)

        End Function

        Public Shared Function CreateCustomerSME(
                                            ByVal TB_ADR_NAME As String,
                                            ByVal TB_ADR_TAXNO As String,
                                            ByVal TB_ADR_COMPNO As String,
                                            ByVal TB_CS_TC_ID As String,
                                            ByVal TB_ADR_FNAME As String,
                                            ByVal TB_ADR_LNAME As String,
                                            ByVal TB_CS_REMARK1 As String,
                                            ByVal TB_CS_BIRTH_PROVINCE As String,
                                            ByVal TB_CS_REMARK2 As String,
                                            ByVal TDD_ADR_BIRTHDT As String,
                                            ByVal TB_CS_REG_PROVINCE As String,
                                            ByVal TB_CS_REG_DISTRICT As String,
                                            ByVal DDL_ADR_SEX As String,
                                            ByVal DDL_IDTYPE_CODE As Integer,
                                            ByVal TB_ADR_IDNO As String,
                                            ByVal DLL_PRG_CODE As String,
                                            ByVal DLL_RPCODE As Integer,
                                            ByVal DLL_RS_CODE As Integer,
                                            ByVal TB_ADR_ROLES As String,
                                            ByVal DDL1_ADR_CITY As String,
                                            ByVal DDL1_ADR_NOTE1 As String,
                                            ByVal TB1_ADR_STREET As String,
                                            ByVal TB1_ADR_ZIP As String,
                                            ByVal TB1_ADR_STREETNO As String,
                                            ByVal TB_ADR_PHN1_AREA As String,
                                            ByVal TB_ADR_PHN1 As String,
                                            ByVal TB_ADR_PHN2_AREA As String,
                                            ByVal TB_ADR_PHN2 As String,
                                            ByVal TB_ADR_SMSNO As String,
                                            ByVal TB_ADR_EMAIL As String,
                                            ByVal TB_ADR_NOTE2 As String,
                                            ByVal LBL_Mernis_Status As String,
                                            ByRef MESSAGE As String) As DataTable





            Dim command As New OracleCommand(dbPackageName & "CreateCustomerSME")

            command.Parameters.Add("pin_ADR_NAME", OracleType.VarChar).Value = TB_ADR_NAME
            command.Parameters.Add("pin_ADR_TAXNO", OracleType.VarChar).Value = TB_ADR_TAXNO
            command.Parameters.Add("pin_ADR_COMPNO", OracleType.VarChar).Value = TB_ADR_COMPNO
            command.Parameters.Add("pin_CS_TC_ID", OracleType.VarChar).Value = TB_CS_TC_ID
            command.Parameters.Add("pin_ADR_FNAME", OracleType.VarChar).Value = TB_ADR_FNAME
            command.Parameters.Add("pin_ADR_LNAME", OracleType.VarChar).Value = TB_ADR_LNAME
            command.Parameters.Add("pin_CS_REMARK1", OracleType.VarChar).Value = TB_CS_REMARK1
            command.Parameters.Add("pin_CS_BIRTH_PROVINCE", OracleType.VarChar).Value = TB_CS_BIRTH_PROVINCE
            command.Parameters.Add("pin_CS_REMARK2", OracleType.VarChar).Value = TB_CS_REMARK2
            command.Parameters.Add("pin_ADR_BIRTHDT", OracleType.VarChar).Value = TDD_ADR_BIRTHDT
            command.Parameters.Add("pin_CS_REG_PROVINCE", OracleType.VarChar).Value = TB_CS_REG_PROVINCE
            command.Parameters.Add("pin_CS_REG_DISTRICT", OracleType.VarChar).Value = TB_CS_REG_DISTRICT
            command.Parameters.Add("pin_ADR_SEX", OracleType.Char).Value = DDL_ADR_SEX
            command.Parameters.Add("pin_IDTYPE_CODE", OracleType.Int32).Value = DDL_IDTYPE_CODE
            command.Parameters.Add("pin_ADR_IDNO", OracleType.VarChar).Value = TB_ADR_IDNO
            command.Parameters.Add("pin_PRG_CODE", OracleType.VarChar).Value = DLL_PRG_CODE
            command.Parameters.Add("pin_RPCODE", OracleType.Int32).Value = DLL_RPCODE
            command.Parameters.Add("pin_RS_CODE", OracleType.Int32).Value = DLL_RS_CODE
            command.Parameters.Add("pin_ADR_ROLES", OracleType.VarChar).Value = TB_ADR_ROLES
            command.Parameters.Add("pin_ADR_CITY", OracleType.VarChar).Value = DDL1_ADR_CITY
            command.Parameters.Add("pin_ADR_NOTE1", OracleType.VarChar).Value = DDL1_ADR_NOTE1
            command.Parameters.Add("pin_ADR_STREET", OracleType.VarChar).Value = TB1_ADR_STREET
            command.Parameters.Add("pin_ADR_ZIP", OracleType.VarChar).Value = TB1_ADR_ZIP
            command.Parameters.Add("pin_ADR_STREETNO", OracleType.VarChar).Value = TB1_ADR_STREETNO
            command.Parameters.Add("pin_ADR_PHN1_AREA", OracleType.VarChar).Value = TB_ADR_PHN1_AREA
            command.Parameters.Add("pin_ADR_PHN1", OracleType.VarChar).Value = TB_ADR_PHN1
            command.Parameters.Add("pin_ADR_PHN2_AREA", OracleType.VarChar).Value = TB_ADR_PHN2_AREA
            command.Parameters.Add("pin_ADR_PHN2", OracleType.VarChar).Value = TB_ADR_PHN2
            command.Parameters.Add("pin_ADR_SMSNO", OracleType.VarChar).Value = TB_ADR_SMSNO
            command.Parameters.Add("pin_ADR_EMAIL", OracleType.VarChar).Value = TB_ADR_EMAIL
            command.Parameters.Add("pin_ADR_NOTE2", OracleType.VarChar).Value = TB_ADR_NOTE2
            command.Parameters.Add("pin_cs_data_level", OracleType.VarChar).Value = LBL_Mernis_Status
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output 'Customer ID Bilgisinin Databaseden Alınması 

            Return ExecuteDataTable(command)



        End Function



        Public Shared Function CreateContract(
                                             ByVal v_customer_id As Integer,
                                             ByVal DDL_RPCODE As Integer,
                                             ByVal DDL_RS_CODE As Integer,
                                             ByVal v_RES_ID As Integer,
                                             ByVal v_DN_ID As Integer,
                                             ByVal pin_rehber As String,
                                             ByVal pin_uluslarasiArama As String,
                                             ByVal pin_uluslarasiDolasim As String,
                                             ByVal dealer_custcode As String,
            ByVal pin_userid As String,
                                             ByRef MESSAGE As String
                                              ) As DataTable


            'Public Shared Function CreateContract(
            '                              ByVal v_customer_id As String,
            '                             ByVal TB_MSISDN As String,
            '                            ByVal TB_DEALER_CODE As String,
            '                           ByVal DDL_RPCODE As String,
            '                          ByVal DDL_RS_CODE As String,
            '                         ByVal CH_INTERNATIONAL_CALL As String,
            '                        ByVal CH_INTERNATIONAL_ROAMING As String,
            '                       ByVal CH_REHBER As String,
            '                      ByVal v_RES_ID As String,
            '                     ByVal v_HLCODE As String,
            '                    ByVal v_SMC_ID As String,
            '                   ByVal v_DN_ID As String,
            '                  ByVal v_DIRNUM As String,
            '                 ByVal v_DN_TYPE As String,
            '                ByVal v_NPCODE As String,
            '               ByRef MESSAGE As String
            '               ) As String()
            '


            Dim command As New OracleCommand(dbPackageName & "CreateContract")



            command.Parameters.Add("pin_customer_id", OracleType.Int32).Value = v_customer_id
            command.Parameters.Add("pin_RPCODE", OracleType.Int32).Value = DDL_RPCODE
            command.Parameters.Add("pin_RS_CODE", OracleType.Int32).Value = DDL_RS_CODE
            command.Parameters.Add("pin_RES_ID", OracleType.Int32).Value = v_RES_ID
            command.Parameters.Add("pin_DN_ID", OracleType.Int32).Value = v_DN_ID
            command.Parameters.Add("pin_rehber", OracleType.VarChar).Value = pin_rehber
            command.Parameters.Add("pin_uluslararasi_arama", OracleType.VarChar).Value = pin_uluslarasiArama
            command.Parameters.Add("pin_uluslararasi_dolasim", OracleType.VarChar).Value = pin_uluslarasiDolasim
            command.Parameters.Add("pin_dealer_custcode", OracleType.VarChar).Value = dealer_custcode
            command.Parameters.Add("pin_userid", OracleType.VarChar).Value = pin_userid
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

            ''LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New GPOPrepaidToGPOPostpaid()
            persistent.myDBConn.ORAScalar_LOG(Query, "null", v_customer_id, "null", "null", "Select(CreateContract)", "Gpo2GpoCustomerCreate", gen.Session("user"))

            Return ExecuteDataTable(command)

        End Function




        Public Shared Function getcustomertype() As DataTable

            Dim command As New OracleCommand(dbPackageName & "getcustomertype")
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function
        Public Shared Function getcustomertypeSME() As DataTable

            Dim command As New OracleCommand(dbPackageName & "getcustomertypeSME")
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function


        Public Shared Function KimlikType() As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetKimlik")
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function aktivasyonType() As DataTable

            Dim command As New OracleCommand(dbPackageName & "aktivasyonnedeni")
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function IlType() As DataTable

            Dim command As New OracleCommand(dbPackageName & "Autocity")
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function IlceType(ByVal city_code As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "Autodistinct")
            command.Parameters.Add("city_code", OracleType.Int32).Value = city_code
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function DealerInfo(ByVal sm_id As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "getdealerinfo")
            command.Parameters.Add("pin_sm_id", OracleType.Int32).Value = sm_id
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function


        Public Shared Function SMECustCodeSearch(ByVal pin_CUSTCODE As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "SMECustSearch")
            command.Parameters.Add("pin_CUSTCODE", OracleType.VarChar).Value = pin_CUSTCODE
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            ''LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New GPOPrepaidToGPOPostpaid()
            persistent.myDBConn.ORAScalar_LOG(Query, pin_CUSTCODE, "null", "null", "null", "Select(SMECustCodeSearch)", "Gpo2GpoCustomerCreate", gen.Session("user"))

            Return ExecuteDataTable(command)

        End Function

        Public Shared Function SMEVergiSearch(ByVal pin_CSCOMPTAXNO As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "SMEVergiSicilSearch")
            command.Parameters.Add("pin_CSCOMPTAXNO", OracleType.VarChar).Value = pin_CSCOMPTAXNO
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function



        Public Shared Function TarifeType(ByVal city_code As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetTariff")
            command.Parameters.Add("pin_prgcode", OracleType.Int32).Value = city_code
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function
        Public Shared Function TarifeTypeSME(ByVal city_code As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetTariffSME")
            command.Parameters.Add("pin_prgcode", OracleType.Int32).Value = city_code
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function


        Public Shared Function GetCustomerInfo(ByVal pin_customer_id As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetCustomerInfo")
            command.Parameters.Add("pin_customer_id", OracleType.Int32).Value = pin_customer_id
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

            Dim Query As String = "Select * from dual "
            Dim customer_id As String = Convert.ToString(pin_customer_id)
            Dim gen As Object = New GPOPrepaidToGPOPostpaid()
            persistent.myDBConn.ORAScalar_LOG(Query, "null", customer_id, "null", "null", "Select(GetCustomerInfo)", "Gpo2GpoCustomerCreate", gen.Session("user"))

            Return ExecuteDataTable(command)


        End Function
        Public Shared Function GetRatePlan(ByVal pin_co_id As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetRatePlan")
            command.Parameters.Add("pin_co_id", OracleType.Int32).Value = pin_co_id
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Dim Query As String = "Select * from dual "
            Dim coid As String = Convert.ToString(pin_co_id)
            Dim gen As Object = New GPOPrepaidToGPOPostpaid()
            persistent.myDBConn.ORAScalar_LOG(Query, "null", "null", coid, "null", "Select(GetRatePlan)", "Gpo2GpoCustomerCreate", gen.Session("user"))

            Return ExecuteDataTable(command)

        End Function
        Public Shared Function GetCustomerCode(ByVal pin_customer_id As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetCustomerCode")
            command.Parameters.Add("pin_customer_id", OracleType.Int32).Value = pin_customer_id
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            ''LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim customer_id As String = Convert.ToString(pin_customer_id)
            Dim gen As Object = New GPOPrepaidToGPOPostpaid()
            persistent.myDBConn.ORAScalar_LOG(Query, "null", customer_id, "null", "null", "Select(GetCustomerCode)", "Gpo2GpoCustomerCreate", gen.Session("user"))

            Return ExecuteDataTable(command)

        End Function
        Public Shared Function GetTariffContract(ByVal customer_id As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetTariffContract")
            command.Parameters.Add("pin_customer_id", OracleType.Int32).Value = customer_id
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            ''LOGLAMA
            Dim log_customer_id As Integer = Convert.ToString(customer_id)
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New GPOPrepaidToGPOPostpaid()
            persistent.myDBConn.ORAScalar_LOG(Query, "null", log_customer_id, "null", "null", "Select(GetCustomerCode)", "Gpo2GpoCustomerCreate", gen.Session("user"))

            Return ExecuteDataTable(command)

        End Function


        Public Shared Function GetMsisdnInfo(ByVal MSISDN As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetMsisdnInfo")
            command.Parameters.Add("pin_MSISDN", OracleType.VarChar).Value = MSISDN
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function GetSIMInfo(ByVal serialnum As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetSIMInfo")
            command.Parameters.Add("pin_sm_serialnum", OracleType.VarChar).Value = serialnum
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function


        Public Shared Function CheckSIM(ByVal serialnum As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "CheckSIM")
            command.Parameters.Add("pin_sm_serialnum", OracleType.VarChar).Value = serialnum
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function


        Public Shared Function GetMsisdn(ByVal serialnum As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetMsisdn")
            command.Parameters.Add("pin_sm_serialnum", OracleType.VarChar).Value = serialnum
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function CountryType() As DataTable

            Dim command As New OracleCommand(dbPackageName & "AutoCountry")
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function GetDealerInfo(ByVal pin_SIM_NO As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetDealerInfo")
            command.Parameters.Add("pin_SIM_NO", OracleType.VarChar).Value = pin_SIM_NO
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function

        Public Shared Function GetRPServiceStatus(ByVal pin_tmcode As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetRPServiceStatus")
            command.Parameters.Add("pin_tmcode", OracleType.Int32).Value = pin_tmcode
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function
        Public Shared Function GetRPCodeInfo(ByVal pin_customer_id As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetRPCodeInfo")
            command.Parameters.Add("pin_customer_id", OracleType.Int32).Value = pin_customer_id
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function
        Public Shared Function DealerControl(ByVal pin_custcode As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "DealerControl")
            command.Parameters.Add("pin_custcode", OracleType.VarChar).Value = pin_custcode
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            Return ExecuteDataTable(command)

        End Function




        Public Shared Function ExecuteNonQuery(ByVal command As OracleCommand, ByRef MESSAGE As String)
            Dim connection As New OracleConnection(ConnStr)
            command.Connection = connection
            command.CommandType = CommandType.StoredProcedure

            Try
                connection.Open()
                command.ExecuteNonQuery()
            Catch ex As Exception
                MESSAGE = ex.Message.ToString
            Finally
                connection.Close()
            End Try
        End Function
        Public Shared Function ExecuteDataTable(ByVal command As OracleCommand, Optional ByVal directSql As Boolean = 0) As DataTable
            Dim connection As New OracleConnection(ConnStr)
            Dim dataAdapter As New OracleDataAdapter(command)
            Dim dataTable As New DataTable
            Dim dataSet As New DataSet

            command.Connection = connection

            If Not (directSql) Then
                command.CommandType = CommandType.StoredProcedure
            End If

            Try
                connection.Open()
                dataAdapter.Fill(dataSet)
                dataTable = dataSet.Tables(0)
            Catch ex As Exception
            Finally
                connection.Close()
                dataSet.Dispose()
            End Try

            Return dataTable
        End Function




    End Class
End Namespace

